clear all

// SET MACROS
global Input  "Y:/limited/Michigan_CTE/funding_change/data_derived/imported"
global Output "Y:/limited/Michigan_CTE/funding_change/data_derived"


// Prep District Year Counts
use ric year grade_fnl bcode_weight dcode_weight using O:/data_final/epi_student/k12_student.dta
keep if inrange(grade,9,12)
gen all = 1
ren (bcode_weight dcode_weight) (bcode dcode)
tempfile temp_all
save `temp_all'

gcollapse (sum)  all, by(bcode year)
 
tempfile temp
save `temp' 
 

// Prep  district file 
use bcode dcode isdcode year using $Input/school_level.dta, clear
replace isdcode = 11000 if bcode==9555
merge 1:1 bcode year using `temp'
bys dcode year: gegen hs_students = total(all)

tempfile temp0
save `temp0'


// Prep yearly coursetaking
use  ${Output}/student_course_data.dta, clear
recode course_per_year (.=2)   //weirdnes in 2019 assume 2
gen fte = 1/course_per_year/6
gcollapse (sum) cte_fte = fte, by(student year)
replace cte_fte = 1 if cte_fte>1

ren student ric
merge 1:1 ric year using `temp_all', keep(3) keepusing(dcode)
gcollapse (sum) cte_fte, by(dcode year)

tempfile temp_fte
save `temp_fte'



// LOAD DATA
use student school district cohort9 cte_any revenue_pp using "Y:/limited/Michigan_CTE/funding_change/data_final/compiled_student_analysis_data.dta", clear
ren school bcode




// Get total HS Students
joinby bcode using `temp0'
keep if inrange(year, cohort9,cohort9+3)
gen sample = year==cohort9+3

* Make sure isdcodes work
replace dcode = district if dcode==.
drop district
replace isdcode = isdcode*1000 if isdcode<100
replace isdcode =  82000 if inlist(dcode,82010,82914) 	// incorrect  throws off code
replace isdcode =  11000 if inlist(dcode,11010) 				// incorrect  throws off code

bys dcode: gegen minisd = min(isdcode)
bys dcode: gegen maxisd = max(isdcode)
replace isdcode = minisd if isdcode==. & minisd==maxisd
assert isdcode!=. 

preserve
gcollapse cte_any, by(dcode year isdcode)
gisid dcode year
restore


* Collapse to district year level
gen sample_students = 1
gcollapse (mean) revenue_pp hs_students  ///
			(sum) sample_students = sample , by(dcode year isdcode)
	
bys year: sum hs_students

merge 1:1 dcode year using `temp_fte', keep(1 3) nogen
recode cte_fte (.=0)
bys year: sum sample_students
bys year: sum cte_fte

ren dcode district
merge m:1 district using "Y:/limited/Michigan_CTE/funding_change/data_derived/temp/district_characteristics.dta"
ren district dcode

* Balance Panel? But if no students it doesn't matter.



***
* Add CTE funding data
***	
	
** Prep CTE funding data		
preserve
use "Y:/limited/Michigan_CTE/funding_change/data_final/psn_funding.dta", clear

ren fano dcode  
gen psn_count = 1

gcollapse (sum) funds* psn_count , by(dcode year cepd)

gen isd = mod(dcode,1000)==0

tempfile temp1 temp2
save `temp1', replace

keep if isd==1
ren (*)(isd*)
ren isdyear year
ren isddcode isdcode
drop isdpsn_count isdisd
save `temp2'

restore

** Merge into dcode
merge 1:1 dcode year using `temp1', gen(d_merge) keep(1 3)
recode funds_state60 funds_cepd40 funds_total psn_count (.=0)
drop isd

** Isdcode and rescale
merge m:1 isdcode year using `temp2', gen(i_merge) keep(1 3)
recode isdfunds_state60 isdfunds_cepd40 isdfunds_total  (.=0)

bys isdcode year: egen isd_enr = total(hs_students)
gen isd_share = hs_students/isd_enr

bys isdcode year: egen isd_cte = total(cte_fte)
gen isdcte_share = cte_fte/isd_cte


foreach v in  isdfunds_state60 isdfunds_cepd40 isdfunds_total {
	gen `v'_n = `v'*isd_share
	gen `v'_fte = `v'*isdcte_share
	
}


/* Add other financial dcode data
merge m:1 dcode year using "O:\data_final\epi_infrastructure\district_level_limited.dta"   , ///
	keep(1 3) keepusing(d_urbanicity  d_pp_local_rev_b1014 d_pp_state_rev_b1014 d_pp_fed_rev_b1014 d_pp_tot_rev_b1014 d_pp_basic_instr_exp_b1014 d_pp_added_needs_exp_b1014 d_pp_trnsprtn_exp_b1014 d_pp_gen_fund_exp_b1014) nogen
*/	
			
			
**** Clean variables
ren revenue_pp cte_millage_pp

ren (funds*) (dcode_cte_funds*)
ren psn_count dcode_psns
ren (isdfunds*) (isd_cte_funds*)



*** Tag on CEPD
preserve	
 use cepd year fano using "Y:/limited/Michigan_CTE/funding_change/data_raw/cte_program_enrollment.dta", clear
 gduplicates drop
 ren fano dcode
 tempfile cepd1 cepd2
 save `cepd1'
 ren dcode isdcode
 save `cepd2'
restore
			
merge m:1 dcode year using `cepd1', keep(1 3 4 5) nogen update
merge m:1 isdcode year using `cepd2', keep(1 3 4 5) nogen update
gen nocepd = cepd==.
replace cepd = 54 if cepd==.

save "Y:/limited/Michigan_CTE/funding_change/data_final/dcode_funding.dta", replace
			
			
			
			
			

			
		